package com.gljx.web.controller.JournalController;


import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.gljx.util.ExcelExport;
import com.gljx.util.TimeFormat;
import com.gljx.web.Entity.JournalEntry.BigCarJournal;
import com.gljx.web.service.BigCarJournalService;

@SuppressWarnings("deprecation")
@Controller
@RequestMapping("/")
public class BigCarJournalController {

	
	@Autowired
	private BigCarJournalService bigCarJournalService;

    //简写单元格创建
    public static HSSFCell setCell(HSSFCell cell, HSSFRow row, HSSFCellStyle cellStyleTitle, Object title, int num) {
        row.setHeightInPoints(23);
        cell = row.createCell(num);
        cell.setCellStyle(cellStyleTitle);
        if (title == null || "".equals(title)) {
            cell.setCellValue(new HSSFRichTextString(""));
        } else {
            cell.setCellValue(new HSSFRichTextString(title.toString()));
        }
        return cell;
    }

    /**
	 * 根据时间查询当日台账
	 * @param request
	 * @return
	 */
	@RequestMapping("/queryBigCarJournal/look.action")
	public String queryBigCarJournal(HttpServletRequest request,String time,String type){
		BigCarJournal bigCarJournal = new BigCarJournal();
		if(time == null || "".equals(time)){
			//获取当天时间
			time = TimeFormat.getNowTime();
			//根据当天时间查询
			bigCarJournal = bigCarJournalService.queryBigCarByTime(time);
		}else{
			//根据时间查询
			bigCarJournal = bigCarJournalService.queryBigCarByTime(time);
		}
		//把数据传到页面
		request.setAttribute("time", time);
		request.setAttribute("type", type);
		request.setAttribute("bigCarJournal", bigCarJournal);
		return "/backHome/journaling/financialJournaling/bigCarJournal/main";
	}
	
	/**
	 * 跳转到添加页面
	 * @param request
	 * @return
	 */
	@RequestMapping("/insertBigCarJournal")
	public String insertBigCarJournal(HttpServletRequest request){
		List<BigCarJournal> bigCarJournals = new ArrayList<>();
		//获取一个月的时间
		List<String> time = TimeFormat.getDayListOfMonth(new Date());
		for (String time2 : time) {
			BigCarJournal bigCarJournal = bigCarJournalService.queryBigCarByTime(time2);
			if(bigCarJournal == null){
				bigCarJournals.add(new BigCarJournal(time2));
			}else{
				bigCarJournals.add(bigCarJournal);
			}
		}
		request.setAttribute("bigCarJournals", bigCarJournals);
		return "/backHome/journaling/financialJournaling/bigCarJournal/add";
	}
	
	/**
	 * 添加数据
	 * @param request
	 * @return
	 */
	@RequestMapping("/addBigCarJournal")
	public String addBigCarJournal(HttpServletRequest request){
		//考试时间
        String[] ksrq = request.getParameterValues("ksrq");
        //约考人数
        String[] ykrs = request.getParameterValues("ykrs");
        //未到人数
		String[] wdrs = request.getParameterValues("wdrs");
		//实考人数
		String[] skrs = request.getParameterValues("skrs");
		//及格人数
		String[] jgrs = request.getParameterValues("jgrs");
		//及格率
		String[] jgl = request.getParameterValues("jgl");
		//本市考生实考人数
		String[] bssk = request.getParameterValues("bssk");
		//备注
		String[] cfdesc = request.getParameterValues("cfdesc");
		List<BigCarJournal> bigCarJournals = new ArrayList<>();
		for (int i = 0; i < ksrq.length; i++) {
			if("".equals(ykrs[i]) && "".equals(wdrs[i]) && "".equals(skrs[i]) && "".equals(jgrs[i]) && "".equals(bssk[i])){

			}else{
				BigCarJournal bigCarJournal = new BigCarJournal();
				bigCarJournal.setKsrq(ksrq[i]);
				if("".equals(ykrs[i])){

				}else{
					bigCarJournal.setYkrs(new BigDecimal(ykrs[i]));
				}
				if("".equals(wdrs[i])){

				}else{
					bigCarJournal.setWdrs(new BigDecimal(wdrs[i]));
				}
				if("".equals(skrs[i])){

				}else{
					bigCarJournal.setSkrs(new BigDecimal(skrs[i]));
				}
				if("".equals(jgrs[i])){

				}else{
					bigCarJournal.setJgrs(new BigDecimal(jgrs[i]));
				}
				bigCarJournal.setJgl(jgl[i]);
				bigCarJournal.setCfdesc(cfdesc[i]);
				if("".equals(bssk[i])){

				}else{
					bigCarJournal.setBssk(new BigDecimal(bssk[i]));
				}
				bigCarJournals.add(bigCarJournal);
			}
		}
		//添加
        int i = bigCarJournalService.addBigCarJournal(bigCarJournals, ksrq[0], ksrq[ksrq.length - 1]);
        //重定向到查询页面
        return "redirect:/DrivingSys/queryBigCarJournal/look.action?type=" + i + "";
    }
	
	/**
	 * 导出数据
	 * @param request
	 * @param response
	 * @param time
	 * @return
     * @throws Exception
     */
	@RequestMapping("/importBigCarJournalData")
	public String importBigCarJournalData(HttpServletRequest request,HttpServletResponse response,String time) throws Exception{
		//根据时间查询
		BigCarJournal bigCarJournal = bigCarJournalService.queryBigCarByTime(time);
        String fileName = "大车科目 二 考试数据每日台帐表.xls";
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        // 定义单元格报头
        String worksheetTitle = "南昌市机动车驾驶人大车科目二考试数据每日台帐"+"\n"+"考场名称：大中型客货车考场       考试时间："+time+"";
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建单元格样式
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定当单元格内容显示不下时自动换行
        cellStyleTitle.setWrapText(true);
        //设置背景颜色
		cellStyleTitle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        //填充背景颜色
		cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        // ------------------------------------------------------------------
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定当单元格内容显示不下时自动换行
        cellStyle.setWrapText(true);
        //设置边框
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

        // ------------------------------------------------------------------
        HSSFCellStyle cellStyle2 = wb.createCellStyle();
        // 指定当单元格内容显示不下时自动换行
        cellStyle2.setWrapText(true);
        // ------------------------------------------------------------------
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyleTitle.setFont(font);
        String ykrs = "约考人数";
		String wdrs = "未到人数";
		String skrs = "实考人数";
		String jgrs = "及格人数";
		String jgl = "及格率";
		String bssk = "本市考生实考人数";

		//第一行
        HSSFSheet sheet = wb.createSheet();
        ExcelExport exportExcel = new ExcelExport(wb, sheet);
        // 创建报表头部
        exportExcel.createNormalHead3(worksheetTitle, 1);

        // 定义第一行
        HSSFRow row1 = sheet.createRow(1);
		HSSFCell cell = row1.createCell(0);
		//设置宽度
		sheet.setColumnWidth(0,ykrs.getBytes().length*2*350);
		sheet.setColumnWidth(1,ykrs.getBytes().length*2*350);
		if(bigCarJournal == null){

		}else{
			//定义第一行第1列
			cell = setCell(cell, row1, cellStyle, ykrs, 0);
			//定义第一行第1列
			cell = setCell(cell, row1, cellStyle, bigCarJournal.getYkrs(), 1);

            // 定义第二行
            HSSFRow row2 = sheet.createRow(2);
			HSSFCell cell2 = row2.createCell(0);
			//定义第一行第1列
			cell2 = setCell(cell2, row2, cellStyle, "", 0);
			//定义第一行第1列
			cell2 = setCell(cell2, row2, cellStyle, "", 1);

            // 定义第三行
            HSSFRow row3 = sheet.createRow(3);
			HSSFCell cell3 = row3.createCell(0);
			//定义第一行第1列
			cell3 = setCell(cell3, row3, cellStyle, wdrs, 0);
			//定义第一行第1列
			cell3 = setCell(cell3, row3, cellStyle, bigCarJournal.getWdrs(), 1);

            // 定义第四行
            HSSFRow row4 = sheet.createRow(4);
			HSSFCell cell4 = row4.createCell(0);
			//定义第一行第1列
			cell4 = setCell(cell4, row4, cellStyle, "", 0);
			//定义第一行第1列
			cell4 = setCell(cell4, row4, cellStyle, "", 1);

            // 定义第五行
            HSSFRow row5 = sheet.createRow(5);
			HSSFCell cell5 = row5.createCell(0);
			//定义第一行第1列
			cell5 = setCell(cell5, row5, cellStyle, skrs, 0);
			//定义第一行第1列
			cell5 = setCell(cell5, row5, cellStyle, bigCarJournal.getSkrs(), 1);

            // 定义第六行
            HSSFRow row6 = sheet.createRow(6);
			HSSFCell cell6 = row6.createCell(0);
			//定义第一行第1列
			cell6 = setCell(cell6, row6, cellStyle, "", 0);
			//定义第一行第1列
			cell6 = setCell(cell6, row6, cellStyle, "", 1);

            // 定义第七行
            HSSFRow row7 = sheet.createRow(7);
			HSSFCell cell7 = row7.createCell(0);
			//定义第一行第1列
			cell7 = setCell(cell7, row7, cellStyle, jgrs, 0);
			//定义第一行第1列
			cell7 = setCell(cell7, row7, cellStyle, bigCarJournal.getJgrs(), 1);

            // 定义第八行
            HSSFRow row8 = sheet.createRow(8);
			HSSFCell cell8 = row8.createCell(0);
			//定义第一行第1列
			cell8 = setCell(cell8, row8, cellStyle, "", 0);
			//定义第一行第1列
			cell8 = setCell(cell8, row8, cellStyle, "", 1);

            // 定义第九行
            HSSFRow row9 = sheet.createRow(9);
			HSSFCell cell9 = row9.createCell(0);
			//定义第一行第1列
			cell9 = setCell(cell9, row9, cellStyle, jgl, 0);
			//定义第一行第1列
			cell9 = setCell(cell9, row9, cellStyle, bigCarJournal.getJgl(), 1);

            // 定义第十行
            HSSFRow row10 = sheet.createRow(10);
			HSSFCell cell10 = row10.createCell(0);
			//定义第一行第1列
			cell10 = setCell(cell10, row10, cellStyle, "", 0);
			//定义第一行第1列
			cell10 = setCell(cell10, row10, cellStyle, "", 1);

            // 定义第十一行
            HSSFRow row11 = sheet.createRow(11);
			HSSFCell cell11 = row11.createCell(0);
			//定义第一行第1列
			cell11 = setCell(cell11, row11, cellStyle, bssk, 0);
			//定义第一行第1列
			cell11 = setCell(cell11, row11, cellStyle, bigCarJournal.getBssk(), 1);

            // 定义第十二行
            HSSFRow row12 = sheet.createRow(12);
			HSSFCell cell12 = row12.createCell(0);
			//定义第一行第1列
			cell12 = setCell(cell12, row12, cellStyle, "", 0);
			//定义第一行第1列
			cell12 = setCell(cell12, row12, cellStyle, "", 1);

            // 定义第十三行
            HSSFRow row13 = sheet.createRow(13);
			HSSFCell cell13 = row13.createCell(0);
			//定义第一行第1列
			cell13 = setCell(cell13, row13, cellStyle, "", 0);
			//定义第一行第1列
			cell13 = setCell(cell13, row13, cellStyle, "", 1);

            // 定义第十四行
            HSSFRow row14 = sheet.createRow(14);
			HSSFCell cell14 = row14.createCell(0);
			//定义第一行第1列
			cell14 = setCell(cell14, row14, cellStyle, "", 0);
			//定义第一行第1列
			cell14 = setCell(cell14, row14, cellStyle, "", 1);

            // 定义第十五行
            HSSFRow row15 = sheet.createRow(15);
			HSSFCell cell15 = row15.createCell(0);
			//定义第一行第1列
			cell15 = setCell(cell15, row15, cellStyle, "", 0);
			//定义第一行第1列
			cell15 = setCell(cell15, row15, cellStyle, "", 1);

            // 定义第十六行
            HSSFRow row16 = sheet.createRow(16);
			HSSFCell cell16 = row16.createCell(0);
			//定义第一行第1列
			cell16 = setCell(cell16, row16, cellStyle, "", 0);
			//定义第一行第1列
			cell16 = setCell(cell16, row16, cellStyle, "", 1);

            // 定义第十七行
            HSSFRow row17 = sheet.createRow(17);
			HSSFCell cell17 = row17.createCell(0);
			//定义第一行第1列
			cell17 = setCell(cell17, row17, cellStyle, "", 0);
			//定义第一行第1列
			cell17 = setCell(cell17, row17, cellStyle, "", 1);

            // 定义第十八行
            HSSFRow row18 = sheet.createRow(18);
			HSSFCell cell18 = row18.createCell(0);
			//定义第一行第1列
			cell18 = setCell(cell18, row18, cellStyle, "", 0);
			//定义第一行第1列
			cell18 = setCell(cell18, row18, cellStyle, "", 1);

            // 定义第十九行
            HSSFRow row19 = sheet.createRow(19);
			HSSFCell cell19 = row19.createCell(0);
			//定义第一行第1列
			cell19 = setCell(cell19, row19, cellStyle, "", 0);
			//定义第一行第1列
			cell19 = setCell(cell19, row19, cellStyle, "", 1);

            // 定义第二十行
            HSSFRow row20 = sheet.createRow(20);
			HSSFCell cell120 = row20.createCell(0);
			//定义第一行第1列
			cell120 = setCell(cell120, row20, cellStyle2, "考试员 ：贺一兵  ", 0);
			//定义第一行第1列
			cell120 = setCell(cell120, row20, cellStyle2, "签名：        ", 1);
		}


        try {
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
            //System.out.println("Output   is   closed ");
        }
        return null;
	}
	
	/**
	 * 查询月报表
	 * @param request
	 * @return
	 */
	@RequestMapping("/queryMonthBigCarJournal/look.action")
	public String queryMonthBigCarJournal(HttpServletRequest request,String time){
		//时间格式化
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
		if(time == null || "".equals(time)){
			//获取当前月
			 time = sdf.format(new Date());
		}
		//获取开始时间
		String startTime = time+"-01";
		//获取结束时间
		String endTime = time+"-31";
		//查询数据
		List<BigCarJournal> bigCarJournals = bigCarJournalService.queryBigCarByOneMonth(startTime, endTime);
		//把数据传到页面
		request.setAttribute("bigCarJournals", bigCarJournals);
		request.setAttribute("time", time);
		return "/backHome/journaling/financialJournaling/bigCarJournal/list";
	}
	
	/**
	 * 导出一个月数据
	 * @param request
	 * @param response
	 * @param time
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("/importMonthBigCarJournalData")
	public String importMonthBigCarJournalData(HttpServletRequest request,HttpServletResponse response,String time) throws Exception{
		//获取开始时间
		String startTime = time+"-01";
		//获取结束时间
		String endTime = time+"-31";
		//查询数据
		List<BigCarJournal> bigCarJournals = bigCarJournalService.queryBigCarByOneMonth(startTime, endTime);
		String fileName = "大车科目二考试数据每日台帐表.xls";  
		fileName = new String(fileName.getBytes("GBK"), "iso8859-1");  
		response.reset();  
		response.setHeader("Content-Disposition", "attachment;filename="  
				+ fileName);// 指定下载的文件名  
		response.setContentType("application/vnd.ms-excel");  
		response.setHeader("Pragma", "no-cache");  
		response.setHeader("Cache-Control", "no-cache");  
		response.setDateHeader("Expires", 0);  
		OutputStream output = response.getOutputStream();
		BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);  
		// 定义单元格报头  
		String worksheetTitle = "南昌市机动车驾驶人大车科目二考试月台帐"+"\n"+"考场名称：大中型客货车考场       考试时间："+time+"";
		HSSFWorkbook wb = new HSSFWorkbook();  
		// 创建单元格样式  
		HSSFCellStyle cellStyleTitle = wb.createCellStyle();  
		// 指定单元格居中对齐  
		cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
		// 指定单元格垂直居中对齐  
		cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
		// 指定当单元格内容显示不下时自动换行  
		cellStyleTitle.setWrapText(true); 
		 //设置背景颜色
		cellStyleTitle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        //填充背景颜色
		cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		//设置边框
		cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
		cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		// ------------------------------------------------------------------  
		HSSFCellStyle cellStyle = wb.createCellStyle();  
		// 指定单元格居中对齐  
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
		// 指定单元格垂直居中对齐  
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
		// 指定当单元格内容显示不下时自动换行  
		cellStyle.setWrapText(true);  
		//设置边框
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		
		// ------------------------------------------------------------------  
		HSSFCellStyle cellStyle2 = wb.createCellStyle();  
		// 指定当单元格内容显示不下时自动换行  
		cellStyle2.setWrapText(true);  
		// ------------------------------------------------------------------  
		// 设置单元格字体  
		HSSFFont font = wb.createFont();  
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
		font.setFontName("宋体");  
		font.setFontHeight((short) 200);  
		cellStyleTitle.setFont(font);  
		String ksrq = "日期";
		String bssk = "本市实考人数";
		String cfdesc = "备注";
		//第一行
		HSSFSheet sheet = wb.createSheet();  
		ExcelExport exportExcel = new ExcelExport(wb, sheet);  
		// 创建报表头部  
		exportExcel.createNormalHead3(worksheetTitle, 2);  
		//设置宽度
		sheet.setColumnWidth(0,8000);
		sheet.setColumnWidth(1,5000);
		sheet.setColumnWidth(2,5500);
		
		// 定义第一行  
		HSSFRow row1 = sheet.createRow(1);
		HSSFCell cell = row1.createCell(0);
		//定义第一行第1列
		cell = setCell(cell, row1, cellStyleTitle, ksrq, 0);
		//定义第一行第1列
		cell = setCell(cell, row1, cellStyleTitle, bssk, 1);
		//定义第一行第1列
		cell = setCell(cell, row1, cellStyleTitle, cfdesc, 2);
		
		//写入数据
        HSSFRow row = sheet.createRow(2);  
        HSSFCell hssfCell = row.createCell(0); 
        for(int i = 0; i < bigCarJournals.size(); i++){
        	BigCarJournal bigCarJournal = new BigCarJournal();
        	//获取数据
        	bigCarJournal = bigCarJournals.get(i);
			row = sheet.createRow(i + 2); 
			//第i行第1列
			hssfCell = setCell(hssfCell, row, cellStyle, bigCarJournal.getKsrq(), 0);
			//第i行第2列
			hssfCell = setCell(hssfCell, row, cellStyle, bigCarJournal.getBssk(), 1);
			//第i行第3列
			hssfCell = setCell(hssfCell, row, cellStyle, bigCarJournal.getCfdesc(), 2);
        }
		if(bigCarJournals.size() == 0 || bigCarJournals == null){
			
		}else{
			  //设置数据下面一行
		       int lastRow = bigCarJournals.size() + 2;
				//写入数据
		       HSSFRow row2 = sheet.createRow(lastRow);  
		       HSSFCell hssfCell2 = row2.createCell(0); 
		       sheet.addMergedRegion(new   Region(lastRow,(short)0,lastRow,(short)2)); 
		       sheet.addMergedRegion(new   Region(lastRow+1,(short)1,lastRow+1,(short)2)); 
		       sheet.addMergedRegion(new   Region(lastRow+2,(short)0,lastRow+2,(short)2));
		       sheet.addMergedRegion(new   Region(lastRow+3,(short)0,lastRow+3,(short)2));
		       sheet.addMergedRegion(new   Region(lastRow+4,(short)1,lastRow+4,(short)2));
			   //第i行第1列
		       hssfCell2 = setCell(hssfCell2, row2, cellStyle2, "车管所带班科长：", 0);
				//写入数据
		       HSSFRow row3 = sheet.createRow(lastRow+1);  
		       HSSFCell hssfCell3 = row3.createCell(0);
			   //第i+2行第1列
		       hssfCell3 = setCell(hssfCell3, row3, cellStyle2, "车管所约考科长：", 0);
			   //第i+2行第2列
		       hssfCell3 = setCell(hssfCell3, row3, cellStyle2, "考场负责人：", 1);
			   //写入数据
		       HSSFRow row4 = sheet.createRow(lastRow+2);  
		       HSSFCell hssfCell4 = row4.createCell(0);
			   //第i+3行第1列
		       hssfCell4 = setCell(hssfCell4, row4, cellStyle2, "分管副所长：", 0);
			   //写入数据
		       HSSFRow row5 = sheet.createRow(lastRow+3);  
		       HSSFCell hssfCell5 = row5.createCell(0);
			   //第i+4行第1列
		       hssfCell5 = setCell(hssfCell5, row5, cellStyle2, "教导员：	", 0);
			   //写入数据
		       HSSFRow row6 = sheet.createRow(lastRow+4);  
		       HSSFCell hssfCell6 = row6.createCell(0);
			   //第i+4行第1列
		       hssfCell6 = setCell(hssfCell6, row6, cellStyle2, "车管所所长： ", 0);
			   //第i+4行第2列
		       hssfCell6 = setCell(hssfCell6, row6, cellStyle2, "考场所属公司法人代表： ", 1);
		}
		try {  
			bufferedOutPut.flush();  
			wb.write(bufferedOutPut);  
			bufferedOutPut.close();  
		} catch (IOException e) {  
			e.printStackTrace();  
			System.out.println("Output   is   closed ");  
		}finally {
			bigCarJournals.clear();
		}
		return null;
	}
	
}
